let express = require('express');
let router = express.Router();
let got = require('got');
const net = require('net');
let qs = require('querystring');
let mysql = require('mysql');
var https = require('https');
let fs = require("fs");
var Global = require('../Global');
const request = require('request')
var sql01 = require('./../db/connction'); //连接池
const {
  resolve
} = require('path');
var url = require('url');
const {
  rejects
} = require('assert');
// 使用DBConfig.js的配置信息创建一个MySQL连接池(废弃)
let pool = mysql.createPool(sql01.mysql);

require('dotenv').config(); //环境变量,需要创建.env文件 配置 使用process.env.secretId
var SMS = require('./../access/TencentSMS'); //封装的腾讯短信接口
let RSAencrypt = require('./../access/enRSA') //RSA加密
let RSAdecrypt = require('./../access/deRSA') //RSA解密
// 引入jwt token工具
const JwtUtil = require('../access/utils/jwt');
const path = require('path');
//连接池
const connectHandler = require('./../db/db.config') //引入连接池
var asyncutil = require('async');



Date.prototype.Format = function (fmt) {
  var o = {
    "M+": this.getMonth() + 1, //月份   
    "d+": this.getDate(), //日   
    "h+": this.getHours(), //小时   
    "m+": this.getMinutes(), //分   
    "s+": this.getSeconds(), //秒   
    "q+": Math.floor((this.getMonth() + 3) / 3), //季度   
    "S": this.getMilliseconds() //毫秒   
  };
  if (/(y+)/.test(fmt))
    fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
  for (var k in o)
    if (new RegExp("(" + k + ")").test(fmt))
      fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
  return fmt;
}

//取得公钥
function getpubkey() {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {

      var sql = "select pubkey from appkey where name='rsa' and used=1 ORDER BY id desc limit 0,1";

      connection.query(sql, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve({
            finish: true,
            data: data[0]["pubkey"]
          });
          connection.release();
        }
      });
    });
  })
}
//取得私钥
function getprikey() {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {

      var sql = "select prikey from appkey where name='rsa' and used=1 ORDER BY id desc limit 0,1";

      connection.query(sql, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve({
            finish: true,
            data: data[0]["prikey"]
          });
          connection.release();
        }
      });
    });
  })
}

function setpubkey(pubkey) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {

      var sql = "update appkey set pubkey=? where name='rsa' and used=1 ORDER BY id desc limit 0,1";
      var addParam = [pubkey];
      connection.query(sql, addParam, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve({
            finish: true
          });
          connection.release();
        }
      });
    });
  })
}

function setprikey(prikey) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {

      var sql = "update appkey set prikey=? where name='rsa' and used=1 ORDER BY id desc limit 0,1";
      var addParam = [prikey];
      connection.query(sql, addParam, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve({
            finish: true
          });
          connection.release();
        }
      });
    });
  })
}



//【返回体】
//返回格式: resultCode 416请先登录   200正常 
// message 提示
// data 数据


//通过id获取用户信息
function getUserInfo(id) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //UserToken过期了就reject 没过期就返回
      var sql = "select id,username,phone,age,remark,sex,tx,vip,UserToken from userinfo where id=?  order by id desc limit 0,1 ";

      var addSqlParams = [id];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) { //未取得数据
            reject({
              finish: false,
              data: "请重新登录"
            });
          } else {
            resolve({
              finish: true,
              data: data[0]
            });
          }
          connection.release();
        }
      });
    });
  })
}


async function LoginByCookie(Authorization) {
  return new Promise(async (resolve, reject) => {
    //验证token是否存在
    if (!Authorization) {
      //验证失败，登录成功后才后端设置token
      reject({
        finish: false,
        data: '请先登录!'
      });
    } else {
      //验证成功,继续往下执行
      //通过token解析出id,取得信息
      let jwt = new JwtUtil(Authorization);
      let result = jwt.verifyToken();
      // 如果考验通过就next，否则就返回登陆信息不正确
      if (result == 'err') {
        reject({
          finish: false,
          data: '请重新登录!'
        });
        return;
        // res.render('login.html');
      }


      let id = result;

      let UserInfo = await getUserInfo(id).catch(err => {
        return err
      });
      if (!UserInfo.finish) { //报错
        reject({
          finish: false,
          data: UserInfo.data
        });
      } else { //取得数据
        resolve({
          finish: true,
          data: UserInfo.data
        });

      }
    }

  })
}

//有cookie，且在数据库里没过期，返回对应user信息,否则跳转登录页
router.post('/getUserInfo', async function (req, res, next) {
  let authorization = req.headers["authorization"]; //小写!!!
  let LoginStatus = await LoginByCookie(authorization).catch(err => {
    return err
  });
  if (!LoginStatus.finish) { //通过UserToken登录失败
    res.json({
      resultCode: 416,
      message: LoginStatus.data
    });
    return
  } else { //登录成功，返回用户数据
    res.json({
      resultCode: 200,
      message: '登录成功!',
      data: LoginStatus.data
    });
  }


})

function checkUsername(username) {

  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "select count(0) as  Num from userinfo where username=? ";

      var addSqlParams = [username];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            resultCode: 400,
            message: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve(data[0]["Num"]);
          connection.release();
        }
      });
    });
  })

}

router.get('/checkUsername', async function (req, res, next) {

  let username = req.query.username || req.params.username;
  let Num = await checkUsername(username).catch(err => {
    res.json(err);
    return
  });
  res.json({
    resultCode: 200,
    message: '成功',
    data: Num
  });
})

//从数据库取得user加密后的密码
function getPWD(username) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "select password from userinfo where username=?  ";

      var addSqlParams = [username];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) {
            reject({
              finish: false,
              data: '用户名错误'
            })
          } else {
            resolve({
              finish: true,
              data: data[0]["password"]
            });
          }
          connection.release();
        }
      });
    });
  })
}

//取得用户信息返回
function UserInfo(username) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "select * from userinfo where username=?  ";
      var addSqlParams = [username];
      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            message: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve({
            finish: true,
            data: data[0]
          });
          connection.release();
        }
      });
    });
  })
}

//插入用户数据
function register(userinfo) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "insert into userinfo(username,password,phone,sex,tx,area) values(?,?,?,?,?,?)  ";
      var addSqlParams = [userinfo["username"], userinfo["password"], userinfo["phone"], userinfo["sex"] == '' ? 0 : userinfo["sex"], userinfo["tx"], userinfo["area"]];
      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve({
            finish: true,
            data: "注册成功"
          });
          connection.release();
        }
      });
    });
  })
}
//验证信息
function calcregister(sms, phone, RequestId) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {

      var sql = " select count(0) Num from sms where val1=? and phone=? and RequestId=? ";
      var addSqlParams = [sms, phone, RequestId];
      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length > 0 && data[0]["Num"] > 0) {
            resolve({
              finish: true,
              data: "成功"
            });
          } else {
            reject({
              finish: false,
              data: "数据错误"
            });
          }

          connection.release();
        }
      });
    });
  })
}

router.post('/register', async function (req, res, next) {

  let FormData = req.body;
  // {
  //   username: 'a13090037631',
  //   password: 'TWpX4urf+tT6YYX7qGmx+TPXoatnGoFFlEAb9TJJj5ZBo9boJsf5SrPVpgbnEjJf2Ufz1/sDTaB309EyzRTg+z5hA2sIv2P37krx5pwzu2XofLAwjx0LyDLTY5l4lpbMRe9aIdGUG1pqWw275EXWYa+kNG32Y5bUvKNnx+7KeyE=',
  //   phone: '13668438495',
  //   sms: '200223',
  //   sex: '',
  //   fileList: [],
  //   areatext: '河北省/石家庄市/桥西区',
  //   tx: '',
  //   RequestId: '5f0cd968-a521-43ed-9908-50ece0062b78'
  // }

  //1.校验短信验证码sms是否与phone,RequestId对应
  let calcjson = await calcregister(FormData["sms"], FormData["phone"], FormData["RequestId"]).catch(err => {
    return err
  });
  if (!calcjson.finish) {
    res.json({
      resultCode: 400,
      message: calcjson.data
    })
  } else {
    //2.注册
    let userinfo = {};
    userinfo["username"] = FormData["username"];
    userinfo["password"] = FormData["password"];
    userinfo["phone"] = FormData["phone"];
    userinfo["sex"] = FormData["sex"];
    userinfo["area"] = FormData["area"];
    userinfo["tx"] = FormData["tx"];

    let regjson = await register(userinfo).catch(err => {
      return err
    });
    if (!regjson.finish) {
      res.json({
        resultCode: 400,
        message: regjson.data
      })
    } else {
      res.json({
        resultCode: 200,
        message: regjson.data
      })
    }
  }



});


router.post('/Login', async function (req, res, next) {
  let username = req.body.username;
  let enpassword = req.body.password;
  let depassword = RSAdecrypt(enpassword); //RSA解析前端密码
  let endbpasswordjson = await getPWD(username).catch(err => {
    return err
  });
  if (!endbpasswordjson.finish) { //报错，或
    res.json({
      resultCode: 400,
      message: endbpasswordjson.data
    })
    return
  } else { //取得数据库中rsa加密后的密码
    let endbpassword = endbpasswordjson.data;
    let dedbpassword = RSAdecrypt(endbpassword); //RSA解析数据库密码

    if (dedbpassword !== depassword) { //密码错误
      res.json({
        resultCode: 400,
        message: '密码错误!'
      })
      return
    } else { //密码正确
      let userjson = await UserInfo(username).catch(err => {
        return err
      });
      if (!userjson.finish) { //服务器错误
        res.json({
          resultCode: 400,
          message: '服务器错误!',
        })
        return
      } else { //取得用户数据

        let _id = userjson.data["id"];
        // jwt通过用户id生成1个7天有效的token
        // 通过该token new JTW() 可通过jtw封装好的verifyToken 推算出用户id,报错返回err字符串
        // JTW类 生成token 用的rsa私钥，验证token用的rsa公钥
        let jwt = new JwtUtil(_id);
        let longNM = 86400000; //1天 - 毫秒
        let lastDate = new Date(new Date().getTime() + longNM).Format("yyyy-MM-dd hh:mm:ss");
        let token = jwt.generateToken(longNM / 1000 / 60);
        // 将 token 返回给客户端
        // res.cookie("UserToken", token, {
        //   maxAge: 86400000, //一天
        //   //httpOnly: true //前端不可读 防止xss攻击
        //  secure :true,这个属性告诉浏览器仅在请求为https的时候发送cookie。
        // });




        res.json({
          resultCode: 200,
          message: '登录成功!',
          data: {
            data: userjson.data,
            token: token
          }
        });

      }
    }
  }


  //生成TOKEN，返回前端，用户数据返回前端，token存入数据库

  //登录失败，不设置token

  //登录成功，设置cookie token

});


//用于post取值
function getpostData(req) {
  //因为post传值为一段段，所以获取到所有的data后要串联起来，这里先以字符串串联为例子
  var str = "";

  req.on('data', function (data, ) {
    str += data; //串联  data 数据
  });

  //获取完所有数据后执行req.on（‘end’,function(){}）
  req.on('end', function () {
    var json = querystring.parse(str);
    return json;
  })
}

//获取客户端ip
function getClientIP(req) {
  return req.headers['x-forwarded-for'] || // 判断是否有反向代理 IP
    req.connection.remoteAddress || // 判断 connection 的远程 IP
    req.socket.remoteAddress || // 判断后端的 socket 的 IP
    req.connection.socket.remoteAddress;
};



router.post('/getpubkey', async function (req, res, next) {

  let result = await getpubkey().catch(err => {
    return err
  });
  if (result.finish) { //取得
    res.json({
      resultCode: 200,
      message: '成功',
      data: result.data
    });
    return
  } else {
    res.json({
      resultCode: 400,
      message: result.data
    });
    return
  }

});

//判断SMS短信权限   返回 上次发送验证码时间,phone满足再判断ip
//同一IP SMS cd 60s
function LastSMSDate(phone, ip) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "select SMSdate from sms where (phone=? or ip = ? ) and finished =1 ORDER BY SMSdate desc limit 0,1";

      var addSqlParams = [phone, ip];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            resultCode: 400,
            message: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) {
            resolve('');
          } else {
            resolve(data[0]["SMSdate"]);
          }

          connection.release();
        }
      });
    });
  });


}

//将SMS数据写入数据库
function writeSMS(phone, ip, resultjson) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      var addSql = "INSERT INTO sms(phone,ip,SMSdate,Requestid,Error,Context,finished,val1) VALUES(?,?,now(),?,?,?,?,?)";
      var addSqlParams = [phone, ip, resultjson["RequestId"], resultjson["Error"] ? resultjson["Error"] : '', resultjson["SessionContext"] ? resultjson["SessionContext"] : '', resultjson["finished"], resultjson["val1"] ? resultjson["val1"] : ''];

      connection.query(addSql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finished: false,
            message: err.message
          });
          connection.release(); //释放连接
        } else {
          resolve({
            finished: true,
            message: '写入成功！'
          });
          connection.release();
        }
      });
    });
  });
}


//根据RequestId,code 验证短信验证码的正确性
router.post('/calcSMS', async function (req, res, next) {

  let RequestId = req.body.RequestId;
  let code = req.body.code;
  if (!RequestId) {
    res.json({
      resultCode: 400,
      message: '短信发送失败,请稍后再试'
    });
  } else {
    let smscode = await calcRequestId(RequestId).catch(err => {
      return err
    });
    if (smscode.finish) { //取得
      if (smscode.data == code) {
        res.json({
          resultCode: 200,
          message: '成功'
        });
        return
      } else {
        res.json({
          resultCode: 400,
          message: '验证码错误'
        });
        return
      }
    } else {
      res.json({
        resultCode: 400,
        message: smscode.data
      });
      return
    }
  }
});


//根据RequestId 验证验证码
function calcRequestId(RequestId) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "select val1 as code from sms where RequestId=? ";

      var addSqlParams = [RequestId];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) {
            reject({
              finish: false,
              data: '参数错误'
            });
          } else {
            resolve({
              finish: true,
              data: data[0]["code"]
            });
          }
          connection.release();
        }
      });
    });
  })
}


router.post('/sendSMS', async function (req, res, next) {
  //腾讯sms接口,顶部定义client获取不到process.env
  let client = SMS.install(process.env.secretId, process.env.secretKey);

  //取得手机号
  let phone = req.body.phone;
  if (!/^[1-9]{1}[0-9]{10}$/.test(phone)) {
    res.json({
      resultCode: 400,
      message: '手机号不正确!'
    });
    return
  }
  //先判断SMS频率, SMSdata
  let ip = getClientIP(req);
  let lastDate = await LastSMSDate(phone, ip).catch((err) => {
    return ''
  }); //数据库读写,返回上次发送短信成功的时间
  let nowTime = new Date().getTime();

  let lastTime = !lastDate ? 0 : new Date(lastDate).getTime();
  let timer = nowTime - lastTime;
  if (timer < 60000) {
    res.json({
      resultCode: 400,
      message: '冷却时间未到!lastdate:' + new Date(lastDate).Format("yyyy-MM-dd hh:mm:ss")
    });
    return
  }

  let fullphone = "+86" + phone;
  //发送短信
  let randomCode = 100000 + parseInt(Math.random() * 900000); //6位
  //参数
  let params = {
    /* 短信应用ID: 短信SmsSdkAppId在 [短信控制台] 添加应用后生成的实际SmsSdkAppId，示例如1400006666 */
    SmsSdkAppId: "1400554752",
    /* 短信签名内容: 使用 UTF-8 编码，必须填写已审核通过的签名，签名信息可登录 [短信控制台] 查看 */
    SignName: "向朋个人网",
    /* 短信码号扩展号: 默认未开通，如需开通请联系 [sms helper] */
    ExtendCode: "",
    /* 国际/港澳台短信 senderid: 国内短信填空，默认未开通，如需开通请联系 [sms helper] */
    SenderId: "",
    /* 用户的 session 内容: 可以携带用户侧 ID 等上下文信息，server 会原样返回 */
    SessionContext: "您的验证码为：" + randomCode + "，如非本人操作，请忽略本短信！",
    /* 下发手机号码，采用 e.164 标准，+[国家或地区码][手机号]
     * 示例如：+8613711112222， 其中前面有一个+号 ，86为国家码，13711112222为手机号，最多不要超过200个手机号*/
    PhoneNumberSet: [fullphone],
    /* 模板 ID: 必须填写已审核通过的模板 ID。模板ID可登录 [短信控制台] 查看 */
    TemplateId: "1062339",
    /* 模板参数: 若无模板参数，则设置为空*/
    TemplateParamSet: [randomCode],


  }



  //通过SDK发送短信
  client.SendSms(params, async function (err, response) {
    // 请求异常返回，打印异常信息
    if (err) {
      return
    }
    // 请求正常返回，打印response对象
    let resultjson = {

    }
    let strjson = JSON.stringify(response);
    let json = JSON.parse(strjson);
    resultjson["RequestId"] = json.RequestId;
    if (json.Error) { //错误
      resultjson["Error"] = json.Error.Message;
      resultjson["finished"] = 0;
    } else if (json.SendStatusSet[0].Fee < 1) { //未计费,比如 1小时内单个手机号码发送的短信数量超过上限
      resultjson["Error"] = json.SendStatusSet[0].Message;
      resultjson["SessionContext"] = json.SendStatusSet[0].SessionContext;
      resultjson["finished"] = 0;
    } else { //成功扣费
      resultjson["finished"] = 1;
      resultjson["val1"] = randomCode; //变量1 验证码,注册时验证
      resultjson["SessionContext"] = json.SendStatusSet[0].SessionContext;
    }
    //写入数据库
    let writeresult = await writeSMS(phone, ip, resultjson).catch(err => {
      return err
    });

    //返回data
    let redata = {
      RequestId: resultjson["RequestId"]
    }

    if (resultjson["finished"] == 0) {
      redata["errorMsg"] = resultjson["Error"];
      res.json({
        resultCode: 400,
        message: '短信发送失败',
        data: redata
      });
      return
    } else if (!writeresult.finished) {
      redata["errorMsg"] = writeresult.message;
      res.json({
        resultCode: 400,
        message: '短信写入数据库失败',
        data: redata
      });
      return
    } else {
      res.json({
        resultCode: 200,
        message: '发送成功',
        data: redata
      });
      return
    }

  });





  //发送短信----作废
  // request({
  //   url: 'https://sms.tencentcloudapi.com',
  //   method: "POST",
  //   json: true,
  //   headers: {
  //     "content-type": "application/json",
  //   },
  //   body: qs.stringify(params)
  // }, async function (error, response, body) {
  //   if (!error && response.statusCode == 200) {

  //     let resultjson = {

  //     }
  //     let strjson = JSON.stringify(body);
  //     let json = JSON.parse(strjson);
  //     resultjson["RequestId"] = json.Response.RequestId;

  //     if (json.Response.Error) {
  //       resultjson["Error"] = json.Response.Error.Message;
  //       resultjson["finished"] = 0;
  //     } else {
  //       resultjson["finished"] = 1;
  //       resultjson["val1"] = randomCode; //变量1 验证码,注册时验证
  //       resultjson["SessionContext"] = json.Response.SendStatusSet[0].SessionContext;
  //     }
  //     //写入数据库
  //     let writeresult = await writeSMS(phone, ip, resultjson).catch(err => {
  //       return err
  //     });


  //返回data
  // let redata = {
  //   RequestId: resultjson["RequestId"]
  // }

  // if (resultjson["finished"] == 0) {
  //   redata["errorMsg"] = resultjson["Error"];
  //   res.json({
  //     resultCode: 400,
  //     message: '短信发送失败',
  //     data: redata
  //   });
  // } else if (!writeresult.finished) {
  //   redata["errorMsg"] = writeresult.message;
  //   res.json({
  //     resultCode: 400,
  //     message: '短信写入数据库失败',
  //     data: redata
  //   });
  // } else {
  //   res.json({
  //     resultCode: 200,
  //     message: '发送成功',
  //     data: redata
  //   });
  // }


});


function phoneused(phone) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "select username from userinfo where phone=? LIMIT 0,1 ";

      var addSqlParams = [phone];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) {
            resolve({
              finish: true,
              data: '未占用'
            });
          } else {
            reject({
              finish: false,
              data: "该手机号已被" + data[0]["username"] + "占用"
            });
          }
          connection.release();
        }
      });
    });
  })
}

//检验手机号是否被占用
router.post('/phoneused', async function (req, res, next) {
  let phone = req.body.phone;
  let phoneusedjson = await phoneused(phone).catch(err => {
    return err
  });
  if (!phoneusedjson.finish) {
    res.json({
      resultCode: 400,
      message: phoneusedjson.data
    })
  } else {
    res.json({
      resultCode: 200,
      message: phoneusedjson.data
    })
  }
});

//检验是否存在
function ckfonttype(userid, fonttype) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "select count(0) count from  userfonttype where userid=? and id=? ";

      var addSqlParams = [userid, fonttype];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data[0]["count"] == 0) {
            reject({
              finish: false,
              data: '保存失败，已被删除'
            })
          } else {
            resolve({
              finish: true,
              data: '成功'
            });
          }
          connection.release();
        }
      });
    });
  })
}


function savefont(id, imgpath, content, fonttype) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "insert into fontlog(userid,imagepath,content,lastdate,fonttype) values(?,?,?,now(),?)";

      var addSqlParams = [id, imgpath, content, fonttype];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve({
            finish: true,
            data: '成功'
          });

          connection.release();
        }
      });
    });
  })
}

function getsavefont(id, pagesize1, pageindex1, fonttype1) {

  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      let pagesize = parseInt(pagesize1 ? pagesize1 : 10);
      let pageindex = parseInt(pageindex1 ? pageindex1 : 1);
      let fonttype = parseInt(fonttype1 ? fonttype1 : 0);
      //这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。
      var limitbegin = (pageindex - 1) * pagesize;
      var pagesql = '';
      var addSqlParams = [];
      if (fonttype == 0) { //查询全部
        pagesql = "select count(0) as TotalCount  from fontlog where userid=?;select *,DATE_FORMAT(lastdate,'%Y-%m-%d %H:%i:%s') fmtdate from fontlog where id>=(select id from fontlog where userid=? limit ?,1) and userid=? limit ?;"
        addSqlParams = [id, id, limitbegin, id, pagesize];
      } else {
        pagesql = "select count(0) as TotalCount  from fontlog where userid=? and fonttype=?;select *,DATE_FORMAT(lastdate,'%Y-%m-%d %H:%i:%s') fmtdate from fontlog where id>=(select id from fontlog where userid=? and fonttype=? limit ?,1) and userid=? and fonttype=? limit ?;"
        addSqlParams = [id, fonttype, id, fonttype, limitbegin, id, fonttype, pagesize];
      }


      connection.query(pagesql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          let redata = {
            TotalPage: 0,
            TotalCount: 0,
            pageindex: pageindex,
            pagesize: pagesize,
            pagedata: []
          }


          if (data[0][0]["TotalCount"] == 0) {
            resolve({
              finish: true,
              data: redata
            });
          } else {
            let TotalPage = parseInt((parseInt(data[0][0]["TotalCount"]) + pagesize - 1) / pagesize);

            redata["TotalPage"] = TotalPage;
            redata["TotalCount"] = data[0][0]["TotalCount"];
            redata["pagedata"] = data[1];
            resolve({
              finish: true,
              data: redata
            });
          }
          
        }
        connection.release();
      });
    });
  })
}


//保存图片识别的记录
router.post('/savefont', async function (req, res, next) {
  let imgpath = req.body.imgpath;
  let content = req.body.content;
  let fonttype = req.body.fonttype;
  if (!fonttype) {
    res.json({
      resultCode: 400,
      message: '请先选择保存类型',
    });
    return
  }
  fonttype = parseInt(fonttype);

  let authorization = req.headers['authorization'];
  let id = '';
  //验证token是否存在
  if (!authorization) {
    //验证失败，登录成功后才后端设置token
    res.json({
      resultCode: 400,
      message: '请先登录',
    });
    return
  } else {
    //验证成功,继续往下执行
    //通过token解析出id,取得信息
    let jwt = new JwtUtil(authorization);
    let result = jwt.verifyToken();
    // 如果考验通过就next，否则就返回登陆信息不正确
    if (result == 'err') {
      res.json({
        resultCode: 416,
        message: '令牌错误!'
      });
      return;
    } else {
      id = result;
    }
  }

  let ckfonttypejson = await ckfonttype(id, fonttype).catch(err => {
    return err
  });
  if (!ckfonttypejson.finish) {
    res.json({
      resultCode: 400,
      message: savefontjson.data
    });
    return;
  }

  let savefontjson = await savefont(id, imgpath, content, fonttype).catch(err => {
    return err
  });
  if (!savefontjson.finish) {
    res.json({
      resultCode: 400,
      message: savefontjson.data
    });
    return;
  } else {
    res.json({
      resultCode: 200,
      message: savefontjson.data
    });
  }


});

//通过前端传入的加密token，返回userid,
function checkUserByToken(token) {
  return new Promise((resolve, reject) => {
    if (!token) {
      //验证失败，登录成功后才后端设置token
      reject({
        finish: false,
        data: '请先登录'
      })
    } else {
      //验证成功,继续往下执行
      //通过token解析出id,取得信息
      let jwt = new JwtUtil(token);
      let result = jwt.verifyToken();
      // 如果考验通过就next，否则就返回登陆信息不正确
      if (result == 'err') {
        reject({
          finish: false,
          data: '令牌错误'
        })
        return;
      } else {
        resolve({
          finish: true,
          data: result
        })
      }
    }
  })
}

//获取用户图片识别的保存记录
router.post('/getsavefont', async function (req, res, next) {
  let authorization = req.headers['authorization'];
  let id = '';
  //验证token是否存在
  let checkjson = await checkUserByToken(authorization).catch(err => {
    err
  });
  if (!checkjson.finish) {
    res.json({
      resultCode: 416,
      message: checkjson.data
    })
    return
  } else {
    id = checkjson.data;
  }

  let pagesize = parseInt(req.body.pagesize);
  let pageindex = parseInt(req.body.pageindex);
  let fonttype = !req.body.fonttype ? 0 : parseInt(req.body.fonttype);
  let savefontjson = await getsavefont(id, pagesize, pageindex, fonttype).catch(err => {
    return err
  });
  if (!savefontjson.finish) {
    res.json({
      resultCode: 400,
      message: savefontjson.data
    });
    return;
  } else {
    res.json({
      resultCode: 200,
      message: "成功",
      data: savefontjson.data
    });
    return
  }
});




//根据类型返回数据
function apitype(option) {
  return new Promise((resolve, reject) => {
    request(option, function (error, response, body) {
      if (error) {
        reject(error.message);
        return
      }
      resolve(body);
    })
  })
}

//获取视频类型
function getdouyintype() {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      let sql = "select * from douyintype";
      connection.query(sql, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) {
            reject({
              finish: false,
              data: '未能获取到视频类型'
            });
          } else {
            resolve({
              finish: true,
              data: data
            });
          }

        }
        connection.release();
      })
    })
  });
}

//获取从 douyintype 获取type ,typename 分别调用API查询50条数据插入数据库
function getscrollbyapi(apioption, searchdate) {
  return new Promise(async (resolve, reject) => {
    let douyintype = await getdouyintype().catch(err => {
      return err
    });
    if (!douyintype.finish) {
      reject({
        finish: false,
        data: douyintype.data
      })
    } else {
      let arrtype = douyintype.data; //[{ id: 1, typename: '轻漫', type: 'cospa_qing_man' }]
      let addparam = []; //要插入的每条数据[[],[],[]]

      //1.每个类型分别调用取得接口数据push 2 addparam
      for (let i = 0; i < arrtype.length; i++) {
        //取得该类型的50条数据
        let option = apioption;
        option["type"] = arrtype[i]["type"];
        let typeid = arrtype[i]["id"];
        let strquery = qs.stringify(option);
        let url = 'http://apis.juhe.cn/fapig/douyin/billboard?' + strquery;
        let body = await apitype({
          url: url,
          method: 'GET',
          headers: { //设置请求头
            "content-type": "application/json",
          }
        });
        //接口数据处理



        let strifyjson = JSON.stringify(body);
        let resultjson = JSON.parse(strifyjson); //对应data
        let strjson = JSON.parse(body); //非deepjson
        if (strjson.error_code != 0) {
          reject({
            resultCode: 400,
            data: 'api接口错误' + strjson.reason
          })
          return
        } else {


          //let temparr = [];//[{},{}]
          strjson.result.forEach(ele => {
            //修改video
            ele.video_list = ele.video_list.map(jsonarr => {
              let strvideo = JSON.stringify(jsonarr);
              let jsonvideo = JSON.parse(strvideo);
              return jsonvideo;
            });

            ele.video_list.forEach(detail => {
              // let tmpjson ={};
              // tmpjson["nickname"] = ele.nickname;//作者
              // tmpjson["follower_count"] = ele.follower_count;
              // tmpjson["avatar"] = ele.avatar;
              // tmpjson["title"] = detail.title;
              // tmpjson["share_url"] = detail.share_url;
              // tmpjson["item_cover"] = detail.item_cover;
              // temparr.push(tmpjson);

              addparam.push([searchdate, typeid, ele.nickname, ele.follower_count, ele.avatar, detail.title, detail.share_url, detail.item_cover]);
            });
          });

          // temparr.forEach(ele=>{
          //   addparam.push([searchdate,typeid,ele.nickname,ele.follower_count,ele.avatar,ele.title,ele.share_url,ele.item_cover]);
          // });



        }
      }
      //2.所有类型的数据批量存入数据库 50*typenum
      pool.getConnection(function (err, connection) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          })
        } else {
          let addsql = "INSERT into douyin(date,type,nickname,follower_count,avatar,title,share_url,item_cover) values ?";
          let addSqlParams = addparam;
          connection.query(addsql, [addSqlParams], function (err, result) {
            if (err) {
              reject({
                finish: false,
                data: err.message
              })
            } else {
              resolve({
                finish: true,
                data: '成功插入' + result.affectedRows + '条数据!'
              })
            }

          });
        }
        connection.release();
      });

    }
  })

}


//取得首页滚动数据 -1 报错 0 直接从数据库取数 1调用API插入数据
function checkscrolldate(searchdate) {
  return new Promise((resolve, reject) => {
    let now = searchdate;
    //这里我每14天更新一次数据 存入数据库，先查最近date,检查当前date是否超过14天，是则插入，否则跳过
    pool.getConnection(function (err, connection) {

      var sql = "select TO_DAYS(?)-TO_DAYS(date) as dateScan from douyin order by id desc LIMIT 1";
      var saparam = [now];
      connection.query(sql, saparam, function (err, result) {
        if (err) {
          reject({
            code: -1,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) { //第一次使用，直接插入
            resolve({
              code: 1,
              data: '插入'
            });
          } else {
            if (parseInt(data[0]["dateScan"]) >= 15) {
              resolve({
                code: 1,
                data: '插入'
              });
            } else {
              resolve({
                code: 0,
                data: "直接获取数据"
              });
            }

          }
          connection.release();
        }
      });
    });


  });
}


//[{type:12,num:2},{type:7,num:1},{type:8,num:1}] 
//根据总需求量 在douyinuserlog表里获取该用户最近datebetween天的兴趣爱好  ,nostatistics=1的不统计
function getIntrestingTypeAndNum(IntresNum, userid, datebetween) {
  return new Promise((resolve, reject) => {
    if (userid == 0) {
      resolve({
        finish: true,
        data: []
      })
      return
    }

    pool.getConnection(function (err, connection) {
      if (err) {
        reject({
          finish: false,
          data: err.message
        })
      } else {
        //从记录里随机取n条

        var sql = "select * from douyinuserlog where userid=? and ifnull(nostatistics,0) <> 1 and (TO_DAYS(now())-TO_DAYS(date)<=?) order by RAND() limit ?";
        var sqlparam = [userid, datebetween, IntresNum];
        connection.query(sql, sqlparam, function (err, result) {
          if (err) {
            reject({
              finish: false,
              data: err.message
            });
          } else {
            let redata = [];
            let type = [];
            let strdata = JSON.stringify(result);
            let data = JSON.parse(strdata);
            data.forEach(ele => {
              if (type.indexOf(ele["type"]) > -1) {
                for (let i = 0; i < redata.length; i++) {
                  if (redata[i]["type"] == ele["type"]) {
                    redata[i]["num"] = parseInt(redata[i]["num"]) + 1;
                    return
                  }
                }
              } else {
                redata.push({
                  type: ele["type"],
                  num: 1
                });
                type.push(ele["type"]);
              }
            });
            resolve({
              finish: true,
              data: redata
            })
          }

        });
      }
      connection.release(); //释放连接
    });
  })
}

//获取最近成功获取api数据的时间 2021-08-16
function getlastdatedouyin() {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      if (err) {
        reject({
          finish: false,
          data: err.message
        })
      } else {
        //从手机号，Ip地址里取最近验证码发送时间
        var sql = "select DATE_FORMAT(date,'%Y-%m-%d') as lastdate from douyin  order by id desc limit 1";
        connection.query(sql, function (err, result) {
          if (err) {
            reject({
              finish: false,
              data: err.message
            });
          } else {
            let strdata = JSON.stringify(result);
            let data = JSON.parse(strdata);
            resolve({
              finish: true,
              data: data[0]["lastdate"]
            })
          }

        });
      }
      connection.release(); //释放连接
    });
  })
}

//获取主页滚动的数据 这里不需要登录
router.post('/getscroll', async function (req, res, next) {

  let apioption = {
    key: req.body.key,
    size: 5 //聚合api 热门视频榜单, 每个类型前30条 这里是3作者*每个作者10条 共30条数据
  }
  let date = new Date().Format("yyyy-MM-dd");
  //根据当前时间判断   -1 报错 0 直接从数据库取数 1调用API插入数据
  let checkscrolldatejson = await checkscrolldate(date).catch(err => {
    return err
  });
  if (checkscrolldatejson.code == -1) {
    res.json({
      resultCode: 400,
      message: checkscrolldatejson.data
    })
    return
  } else {
    //往数据库存入数据
    if (checkscrolldatejson.code == 1) {
      let scrollbyapi = await getscrollbyapi(apioption, date).catch(err => {
        return err
      });
      if (!scrollbyapi.finish) {
        res.json({
          resultCode: 400,
          message: scrollbyapi.data
        })
        return
      }
    }
    //从数据库读取数据  
    //1.登录用户 取得userid  从douyinuserlog 获取该用户type比例分布（最近50天？）
    // 前端一次取10条数据，70%来自推荐 向上取整，30% 随机 向下取整
    let pagesize = req.body.pagesize;
    let rate = 70; //
    let IntresNum = Math.ceil(pagesize * rate * 0.01); //推荐量
    let RanNum = Math.floor(pagesize * ((100 - rate )* 0.01)); //随机量
    
    //获取最近成功获取api数据的时间 2021-08-16
    let lastdatejson = await getlastdatedouyin().catch(err => {
      return err
    });
    if (!lastdatejson.finish) {
      res.json({
        resultCode: 400,
        message: lastdatejson.data
      })
      return
    }
    let lastdate = lastdatejson.data;

    //用户id，查用户兴趣爱好要用到。非必须，如果未登录，为0
    let authorization = req.headers['authorization'];
    let userid = 0;
    if (authorization != '' && authorization != null && authorization != 'null' && authorization != undefined) {
      let checkjson = await checkUserByToken(authorization).catch(err => {
        return err
      });
      //验证token是否存在
      if (!checkjson.finish) {
        res.json({
          resultCode: 416,
          message: checkjson.data
        })
        return
      } else {
        userid = checkjson.data;
      }
    }
    let datebetween = 60; //只查最近60天
    //获取兴趣列表  未登录则恒返回[]
    let Intrestingjson = await getIntrestingTypeAndNum(IntresNum, userid, datebetween).catch(err => {
      return err;
    });
    if (!Intrestingjson.finish) {
      res.json({
        resultCode: 400,
        message: Intrestingjson.data
      })
      return
    }
    //[{type:12,num:2},{type:7,num:1},{type:8,num:1}] 
    let Intresting = Intrestingjson.data;
    if (Intresting.length == 0) {
      RanNum = pagesize;
      IntresNum = 0;
      //全随机
      //select * from douyin where TO_DAYS(date)=TO_DAYS(lastdate)  order by rand() limit RanNum 
      //获取返回的数据


    } else {
      let num = 0;
      Intresting.forEach(ele=>{
        num+=parseInt(ele.num)
      });
      IntresNum = num;

      RanNum = (parseInt(pagesize) - IntresNum);
      

      //  redata = []; 最终返回的数据
      //  for( i  Intresting.length){
      //    select * from douyin where type=Intresting[i][type] and TO_DAYS(date)=TO_DAYS(lastdate) order by rand() limit Intresting[i][num]
      //    for jsondata = result.length =>json
      //      redata.push(jsondata)
      //  }
      //  outtype =[]
      //  outtype = Intresting.map ele  return ele.type
      //  outtypeid = outtype.join(',')
      //  select * from douyin where type not in (outtypeid) and TO_DAYS(date)=TO_DAYS(lastdate)  order by rand() limit RanNum 
      //    for
      //    redata.push(jsondata)



    }

    
    let IntrestingDatajson =await getIntrestingData(lastdate, Intresting, RanNum).catch(err => {
      return err
    });
    
    if (!IntrestingDatajson.finish) {
      res.json({
        resultCode: 400,
        message: IntrestingDatajson.data
      })
      return
    } else {
      res.json({
        resultCode: 200,
        message: '成功',
        data: IntrestingDatajson.data
      });
      return
    }

  }



});

//保存用户浏览记录
function saveuserscrolllog(userid,type,videoid){
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      if (err) {
        reject({
          finish: false,
          data: err.message
        })
      } else {
       
        var sql = "INSERT into douyinuserlog(videoid,userid,type,date) values(?,?,?,now())";
        var addparam = [videoid,userid,type];
        connection.query(sql, addparam,function (err, result) {
          if (err) {
            reject({
              finish: false,
              data: err.message
            });
          } else {
            resolve({
              finish: true,
              data: '成功插入' + result.affectedRows + '条数据!',
            })
          }

        });
      }
      connection.release(); //释放连接
    });
  })
}

router.get('/savelog', async function (req, res, next) {
  //用户id，查用户兴趣爱好要用到。非必须，如果未登录，为0
  let authorization = req.headers['authorization'];
  let userid = 0;
  if (authorization != '' && authorization != null && authorization != 'null' && authorization != undefined) {
    let checkjson = await checkUserByToken(authorization).catch(err => {
      return err
    });
    //验证token是否存在
    if (!checkjson.finish) {
      res.json({
        resultCode: 416,
        message: checkjson.data
      })
      return
    } else {
      userid = checkjson.data;
    }
  }

  let query = req.query?req.query:req.params;
  let type = query.type;
  let videoid = query.id;
  //不保存未登录用户的浏览记录
  if(userid==0){
    res.json({
      resultCode: 400,
      message: checkjson.data
    })
    return
  }else{
    let userscrolllogjson =await saveuserscrolllog(userid,type,videoid).catch(err=>{return err});
    if(!userscrolllogjson.finish){
      res.json({
        resultCode: 400,
        message: userscrolllogjson.data
      })
      return
    }else{
      res.json({
        resultCode: 200,
        message: '记录成功',
        data:userscrolllogjson.data
      })
      return
    }
  }


});

//根据结算期，兴趣爱好[{type:2,num:1},...],随机取值数量    数据量小可能会有重复
function getIntrestingData(lastdate, Intresting, RanNum1) {
  
  return new Promise((resolve, reject) => {
    
    pool.getConnection(async function (err, connection) {
      if (err) {
        reject({
          finish: false,
          data: err.message
        });
        return
      } else {

        let RanNum = RanNum1;
        let redata = []; //最终返回的数据
        let outtypeid = '';
        if (Intresting.length > 0) {
          for (let i = 0; i < Intresting.length; i++) {
            if (i > 0) {
              outtypeid += ',';
            }
            outtypeid += Intresting[i]["type"];
          }
        }
        //遍历兴趣爱好，每种类型取值
        let haserror = false;
        await (()=>{
          for (let j = 0; j < Intresting.length; j++) {
          
            let sql = "select * from douyin where type=? and TO_DAYS(date)=TO_DAYS(?) order by rand() limit ?";
            let sqlparam = [Intresting[j]["type"], lastdate, Intresting[j]["num"]];
            connection.query(sql, sqlparam, function (err, result) {
              if (err) {
                reject({
                  finish: false,
                  data: err.message
                });
                haserror = true;
                connection.release();
                return
              } else {
                var dataString = JSON.stringify(result);
                var data = JSON.parse(dataString);
                if (data.length < Intresting[j]["num"]) { //数据库中，该类型的数据不够取，加到RanNum
                  RanNum += (Intresting[j]["num"] - data.length);
                }
                if (data.length > 0) {
                  redata= redata.concat(data);
                }
              }
            });
          }
        })();
        
        if( haserror ){
          return
        }

        let sql2 ="";
        let sqlparam2 =[];
        if(Intresting.length > 0){
          sql2 = "select * from douyin where type not in (?) and TO_DAYS(date)=TO_DAYS(?)  order by rand() limit ? ";
          sqlparam2 = [outtypeid, lastdate, RanNum];
        }else{
          sql2 = "select * from douyin where  TO_DAYS(date)=TO_DAYS(?)  order by rand() limit ? ";
          sqlparam2 = [lastdate, RanNum];
        }
       
        connection.query(sql2, sqlparam2, function (err, result) {
          if (err) {
            reject({
              finish: false,
              data: err.message
            });
          } else {
            let dataString = JSON.stringify(result);
            let data = JSON.parse(dataString);
            if (data.length < RanNum) { //数据库中，该类型的数据不够取
              reject({
                finish: false,
                data: '数据量不够用了'
              });
              
              
            } else {
              redata = redata.concat(data);
              resolve({
                finish: true,
                data: redata
              })
            }

          }
          connection.release();
        });
      }
     
    });
  });
}


//根据用户ID取出所有该用户所有识别类型，并按照order排序
function getfonttype(id) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "select *,DATE_FORMAT(date,'%Y-%m-%d %H:%i:%s') fmtdate from userfonttype where userid=? order by fmtdate desc";

      var addSqlParams = [id];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          resolve({
            finish: true,
            data: data
          });
          connection.release();
        }
      });
    });
  })
}



//获取用户的文字识别保存类型
router.get('/getfonttype', async function (req, res, next) {
  let authorization = req.headers['authorization'];
  let id = '';
  //验证token是否存在
  let checkjson = await checkUserByToken(authorization).catch(err => {
    err
  });
  if (!checkjson.finish) {
    res.json({
      resultCode: 416,
      message: checkjson.data
    })
    return;
  } else {
    id = checkjson.data;
  }

  let fontypejson = await getfonttype(id).catch(err => {
    return err
  });
  if (!fontypejson.finish) {
    res.json({
      resultCode: 400,
      message: fontypejson.data
    })

  } else {
    res.json({
      resultCode: 200,
      message: '成功',
      data: fontypejson.data
    })
  }


});

function addfonttype(userid, name) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      //从手机号，Ip地址里取最近验证码发送时间
      var sql = "insert into userfonttype(name,userid,date) values(?,?,now())";

      var addSqlParams = [name, userid];

      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
          connection.release(); //释放连接
        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);

          resolve({
            finish: true,
            data: {
              id: data.insertId,
              name: name
            }
          });
          connection.release();
        }
      });
    });
  })
}

//用户添加新的文字识别类型
router.get('/addfonttype', async function (req, res, next) {
  let type = req.query.name;
  if (!type) {
    res.json({
      resultCode: 400,
      message: '参数错误'
    })
    return;
  }

  let authorization = req.headers['authorization'];
  let id = '';
  //验证token是否存在
  let checkjson = await checkUserByToken(authorization).catch(err => {
    err
  });
  if (!checkjson.finish) {
    res.json({
      resultCode: 416,
      message: checkjson.data
    })
    return;
  } else {
    id = checkjson.data;
  }




  let addjson = await addfonttype(id, type).catch(err => {
    return err
  });

  if (!addjson.finish) {
    res.json({
      resultCode: 400,
      message: addjson.data
    })
    return;
  } else {
    res.json({
      resultCode: 200,
      message: '成功',
      data: addjson.data
    })
    return;
  }

})


router.post('/bdimg2font', async function (req, res, next) {

  //因为post传值为一段段，所以获取到所有的data后要串联起来，这里先以字符串串联为例子


  let url = 'https://aip.baidubce.com/rest/2.0/ocr/v1/handwriting?access_token=' + req.body.token;

  request.post({
    url: url,
    form: {
      image: req.body.image
    }
  }, function (error, response, body) {

    let json = JSON.parse(body);
    res.json({
      resultCode: 200,
      message: '成功',
      data: json
    });
  })

});

//图片转文字
router.post('/getFontToken', async function (req, res, next) {

  //文字识别token请求相关参数
  const param = qs.stringify({
    'grant_type': 'client_credentials', //固定
    'client_id': '7svMjuu1BrCQGCmiYt4Al0al', //百度智能云-文字识别应用
    'client_secret': 'DYsDGfX9VZk7N4lFVk6cbpF3Hfiip0Cl' //同上
  });

  let config = {
    hostname: 'aip.baidubce.com',
    path: '/oauth/2.0/token?',
    agent: false,
    param: param
  }

  let tokenFileName = "./baidutoken/fontRead.json"; //token文件名
  //取得token，存入文件

  let token = '';

  token = await calctoken(config, tokenFileName);


  res.json({
    resultCode: 200,
    message: '成功',
    data: token
  });

})

//进行Promise的封装
function fsRead(path) {
  return new Promise((resolve, reject) => {
    fs.readFile(path, "utf-8", (err, data) => {
      if (err) {
        reject(err);
      } //如果有错误就会进行错误的输出
      else {
        resolve(data);
      } //在这里data就是文件的内容（hello）
    });
  });
}


//取得token
function calctoken(config, tokenFileName) {

  return new Promise((resolve, reject) => {
    fs.exists(tokenFileName, async function (exists) {

      if (exists) { //token文件存在
        //判断date是否过期30天
        fsRead(tokenFileName).then(async (data) => {
          let jsondata = JSON.parse(data.toString());
          let createDate = jsondata["createDate"];
          let olddate = new Date(createDate).getTime();
          let nowdate = new Date().getTime();
          //相差天数
          var days = Math.floor((nowdate - olddate) / (24 * 3600 * 1000));
          if (days >= 30) {
            await writeToken2File(config, tokenFileName); //重新设置token文件
            await writeDate(tokenFileName); //设置取得时间

            let token = await readToken(tokenFileName);
            resolve(token);
          } else {
            //直接从文件取得token
            let token = await readToken(tokenFileName);
            resolve(token);
          }
        }).catch(error => {
          reject(error);
        })
      } else {
        //创建token文件
        await writeToken2File(config, tokenFileName).catch(error => console.log(error.message)); //重新设置token文件
        await writeDate(tokenFileName).catch(error => console.log(error.message)); //设置取得时间
        let token = await readToken(tokenFileName).catch(error => console.log(error.message));

        resolve(token);
      }
    })
  });


}

//直接从文件取得token
function readToken(filename) {
  return new Promise((resolve, reject) => {
    fs.readFile(filename, "utf-8", function (error, data) {
      if (error) {
        reject(error);
      } else {
        let jsondata = JSON.parse(data);
        access_token = jsondata["access_token"];
        resolve(access_token);
      }
    });
  })

}

//往文件写入取得token时间
function writeDate(filename) {
  return new Promise((resolve, reject) => {
    fs.readFile(filename, "utf-8", function (error, data) {
      if (error) {
        reject({
          code: 1,
          msg: error
        });
      } else {
        let jsondata = JSON.parse(data.toString());
        jsondata["createDate"] = new Date().getTime();
        fs.writeFile(filename, JSON.stringify(jsondata), (err, data) => {
          if (err) {
            reject({
              code: 1,
              msg: err
            });
          } else {
            resolve({
              code: 0,
              msg: '成功'
            });
          }
        })
      }
    })
  })

}

//token写入文件
function writeToken2File(config, filename) {
  return new Promise((resolve, reject) => {
    https.get({
        hostname: config.hostname,
        path: config.path + config.param,
        agent: config.agent ? config.agent : false
      },
      async function (res) {
        // 在标准输出中查看运行结果
        try {
          res.pipe(process.stdout);

          // 写入文件
          res.pipe(fs.createWriteStream(filename, {
            encoding: 'utf8'
          }));
          resolve("true");
        } catch (error) {
          reject(error);
        }



      }
    );
  })
}



router.get('/', function (req, res, next) {
  res.send("hello world");
})

function apitq(option) {
  return new Promise((resolve, reject) => {
    request(option, function (error, response, body) {
      if (error) {
        reject(error.message);
      }
      resolve(response.body);
    })
  })
}

function getweather(query) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {

      let sql = "select id,city,cityid,DATE_FORMAT(startdate,'%Y-%m-%d') startdate,DATE_FORMAT(enddate,'%Y-%m-%d') enddate,result from cityweather where cityid=? and (TO_DAYS(enddate)-TO_DAYS(now()) = 4) order by id desc limit 1";
      let addparam = [query.city];
      connection.query(sql, addparam, async function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });

        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) { //取得未来5天数据保存到数据库并返回
            let strquery = qs.stringify(query);
            let url = 'http://apis.juhe.cn/simpleWeather/query?' + strquery;
            try {

              let body = await apitq({
                url: url,
                method: 'GET',
                headers: { //设置请求头
                  "content-type": "application/json",
                }
              });

              let strifyjson = JSON.stringify(body);
              let resultjson = JSON.parse(strifyjson);
              let strjson = JSON.parse(body);



              if (strjson["error_code"] != 0) { //接口错误
                reject({
                  finish: false,
                  data: strjson.reason || '接口调用失败' //
                });

              } else {
                //json 数据取得，1.存入数据库 2.返回给前端

                let datejson = {
                  city: strjson.result.city,
                  cityid: query.city,
                  startdate: new Date().Format("yyyy-MM-dd"),
                  enddate: new Date(new Date().getTime() + 60000 * 60 * 24 * 4).Format("yyyy-MM-dd"),
                  data: resultjson
                }

                let strsql = "insert into cityweather(city,startdate,enddate,result,cityid) values(?,?,?,?,?)";
                let sqlparam = [datejson["city"], datejson["startdate"], datejson["enddate"], datejson["data"], datejson["cityid"]];


                connection.query(strsql, sqlparam, function (err2, result) {
                  if (err2) {
                    reject({
                      finish: false,
                      data: err2.message
                    });

                  } else {
                    resolve({
                      finish: true,
                      data: datejson
                    })
                  }
                  connection.release();
                });

              }

            } catch (e) {
              reject({
                finish: false,
                data: '接口调用失败' + e
              });

            }
          } else {

            //返回未来5天数据
            let datejson = {
              city: data[0]["city"],
              cityid: data[0]["cityid"],
              startdate: data[0]["startdate"],
              enddate: data[0]["enddate"],
              data: JSON.parse(data[0]["result"])
            }
            resolve({
              finish: true,
              data: datejson
            });

          }
        }
        
      });
    });




  })

}


//只执行1次，往数据库保存聚合citylist
router.get('/initcity', function (req, res, next) {
  (async () => {
    try {

      let url = 'http://apis.juhe.cn/simpleWeather/cityList?key=0feb4dfde7847f4d2f766a5019ff774c';
      const response = await got(url);

      let jsondata = JSON.parse(response.body);
      let jsonarr = jsondata.result;
      if (jsondata.error_code != 0) {
        res.json({
          resultCode: 400,
          message: '接口错误'
        })
        return
      } else {
        jsonarr = jsonarr.map(ele => {
          return [ele.id, ele.province, ele.city, ele.district];
        });
        pool.getConnection(function (err, connection) {
          //从手机号，Ip地址里取最近验证码发送时间
          var sql = "insert into citylist(cityid,province,city,district) values ?";

          var addSqlParams = jsonarr;

          connection.query(sql, [addSqlParams], function (err, result) {
            if (err) {
              res.json({
                resultCode: 400,
                message: err.message
              });
            } else {
              res.json({
                resultCode: 200,
                message: '成功插入' + result.affectedRows + '条数据!',
                data: result
              })
            }
            connection.release(); //释放连接
          });
        });
      }
    } catch (error) {
      res.json({
        resultCode: 400,
        message: error
      })
    }
  })();
})


/**
 *  用于返回用户的操作日志
 *
 * @param {*} userid1 用户id,可 1,2,3
 * @param {*} datetype1 时间类型 1本周 2本月 3本年 4自定义
 * @param {number} [multi1=0]  是否查多个用户，是则1     
 * @param {number} [typeid1=0] 查询什么类型的日志 0 所有 1抖音 2文字识别
 * @return {*} Promise
 */
function userlog(pagesize1,pageindex1,userid1,typeid1=0,datetype1,multi1=0,datestart1,dateend1){
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      if(err){
        reject({
          finish: false,
          data: err.message
        });
        return
      }

      //参数处理与条件拼接
      let pagesize =  10;
      let pageindex =  1;
      var limitbegin = 0;
      let userid = 0;
      let sqlWhere = '';
      let datetype = '';
      let multi = 0;
      let typeid = 0;
      let datestart ='';
      let dateend ='';
      try{
        pagesize = parseInt(pagesize1 ? pagesize1 : 10);
        pageindex = parseInt(pageindex1 ? pageindex1 : 1);
        limitbegin = (pageindex - 1) * pagesize;

        multi = connection.escape(multi1);
        if(multi==0){
          userid = parseInt(userid1);
          sqlWhere += ` and userid=${connection.escape(userid)} `;
        }else{
          userid = connection.escape(userid1);
          sqlWhere += ` and userid in (${connection.escape(userid)} )`;
        }
        datetype = connection.escape(datetype1);

        if(datetype==1){//本周
          sqlWhere += `  and YEARWEEK(date_format(date,'%Y-%m-%d'),1) = YEARWEEK(now(),1) `;
        }else if(datetype==2){//本月
          sqlWhere += `  and DATE_FORMAT(date, '%Y%m' ) = DATE_FORMAT(CURDATE(), '%Y%m' ) `;
        }else if(datetype==3){//本年
          sqlWhere += `  and YEAR(date)=YEAR(NOW()) `;
        }else if(datetype==4){
          datestart = connection.escape(datestart1);
          dateend = connection.escape(dateend1);
          sqlWhere += `  and date_format(date,'%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(${datestart},'%Y-%m-%d %H:%i:%s') and DATE_FORMAT(${dateend},'%Y-%m-%d %H:%i:%s')   `;
        }
        typeid = connection.escape(typeid1);
        if(typeid!=0){
          sqlWhere += ` and typeid=${connection.escape(typeid)} `;
        }
        

      }catch(e){
        reject({
          finish: false,
          data: err.message
        });
        connection.release();
        return
      }
      
      let sql = `select * from(
        select * from(
        select a.userid as userid,'抖音播放' as type,1 as typeid,a.id as targetid,DATE_FORMAT(a.date,'%Y-%m-%d %H:%i') as date,left(b.title, 32) as title  from douyinuserlog a
        LEFT JOIN douyin b on a.videoid=b.id
        )a
        union all
        SELECT * from(
        select a.userid as userid,'文字识别' as type,2 as typeid,a.id as targetid,DATE_FORMAT(a.lastdate,'%Y-%m-%d %H:%i') as date,concat('('+b.name+')',left(a.content, 10)) as title  from fontlog a
        left JOIN  userfonttype b on a.fonttype=b.id
        )b
        )tbl
        where 1=1 ${sqlWhere}
        order by date desc
        limit ${limitbegin},${pagesize}
        `;
      
      connection.query(sql,   function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });
        }else{
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          let redata = {
            TotalPage: 0,
            TotalCount: 0,
            pageindex: pageindex,
            pagesize: pagesize,
            pagedata: []
          }


          if (data.length == 0) {
            resolve({
              finish: true,
              data: redata
            });
          } else {
            let TotalPage = parseInt((data.length + pagesize - 1) / pagesize);

            redata["TotalPage"] = TotalPage;
            redata["TotalCount"] = data.length;
            redata["pagedata"] = data;
            resolve({
              finish: true,
              data: redata
            });
          }
        }
        connection.release();
      });

    });
  });
}

//获取用户的操作记录
router.post('/userlog', async function (req, res, next) {
  //用户id，查用户兴趣爱好要用到。非必须，如果未登录，为0
  let authorization = req.headers['authorization'];
  let userid = 0;
  if (authorization != '' && authorization != null && authorization != 'null' && authorization != undefined) {
    let checkjson = await checkUserByToken(authorization).catch(err => {
      return err
    });
    //验证token是否存在
    if (!checkjson.finish) {
      res.json({
        resultCode: 416,
        message: checkjson.data
      })
      return
    } else {
      userid = checkjson.data;
    }
  }


  if(userid==0){
    res.json({
      resultCode: 400,
      message: '请先登录'
    })
    return
  }
  let pagesize = parseInt(req.body.pagesize);
  let pageindex = parseInt(req.body.pageindex);
  let datetype = req.body.datetype?parseInt(req.body.datetype):0;
  let typeid = req.body.typeid?parseInt(req.body.typeid):0;
  let userlogjson = await userlog(pagesize,pageindex,userid,typeid,datetype).catch(err=>{return err});
  if(!userlogjson.finish){
    res.json({
      resultCode: 400,
      message: userlogjson.data
    })
    return
  }
  res.json({
    resultCode: 200,
    message:'成功',
    data: userlogjson.data
  })



});

//通过城市（区）名  四川省/成都市/天府新区  查找对应api citylist的cityid返回前端
function fundidbyCityName(name) {
  let arrcity = name.split('/');
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      var sql = "select * from citylist where  locate(province,?)>0 and locate(city,?)>0 and locate(district,?)>0 ORDER BY id limit 1";

      var addSqlParams = [arrcity[0], arrcity[1], arrcity[2]];


      connection.query(sql, addSqlParams, function (err, result) {
        if (err) {
          reject({
            finish: false,
            data: err.message
          });

        } else {
          var dataString = JSON.stringify(result);
          var data = JSON.parse(dataString);
          if (data.length == 0) {
            reject({
              finish: false,
              data: '暂不支持该城市'
            });
          } else {
            resolve({
              finish: true,
              data: {
                id: data[0].cityid,
                name: data[0].district
              }
            });
          }


        }
        connection.release(); //释放连接
      });
    });
  })
}

//四川省/成都市/双流区   =>  citylist cityid
router.get('/cityid', async function (req, res, next) {
  let cityfullname = req.query.cityfullname;
  let cityjson = await fundidbyCityName(cityfullname).catch(err => {
    return err
  });
  if (!cityjson.finish) {
    res.json({
      resultCode: 400,
      message: cityjson.data,
    })
  } else {
    res.json({
      resultCode: 200,
      message: '成功',
      data: cityjson.data
    })
  }


});
//天气 通过cityid查
router.get('/tq', async function (req, res, next) {
  //1.查询数据库里该城市 5天后的数据，查询到了，则直接从数据库取得，否则调用API存入该城市未来5天的天气情况 date temperature weather
  //2.从数据库取得该城市未来5天的数据返回
  let query = req.query ? req.query : req.params;
  let weatherjson = await getweather(query).catch(err => {
    return err
  });
  if (!weatherjson.finish) {
    res.json({
      resultCode: 400,
      message: weatherjson.data,
    })
  } else {
    res.json({
      resultCode: 200,
      message: '成功',
      data: weatherjson.data
    })
  }
})

//新闻头条
router.get('/tt', function (req, res, next) {

  (async () => {
    try {
      let strquery = req.query ? req.query : req.params;
      strquery = qs.stringify(strquery);
      let url = 'http://v.juhe.cn/toutiao/index?' + strquery;
      const response = await got(url);

      let jsondata = JSON.parse(response.body);
      res.json({
        resultCode: 200,
        message: jsondata.reason,
        data: jsondata.result
      })


    } catch (error) {
      res.json({
        resultCode: 400,
        message: error
      })
    }
  })();
})

//查询数据库
router.get('/pageselect', function (req, res, next) {

  pool.getConnection(function (err, connection) {



    let strquery = req.query ? req.query : req.params ? req.params : '';



    var table = strquery['table'] ? strquery['table'] : 'websites';
    var pagesize = parseInt(strquery['pagesize'] ? strquery['pagesize'] : 10);
    var pageindex = parseInt(strquery['pageindex'] ? strquery['pageindex'] : 1);
    var order = strquery['order'] ? strquery['order'] : 'id';



    var sql = 'select * from ' + table + ' where id > ( ?-1)*? order by ? limit ? ';
    var addSqlParams = [pageindex, pagesize, order, pagesize];


    let json = {};

    connection.query(sql, addSqlParams, function (err, result) {
      if (err) {
        json['error_code'] = 1;
        json['result'] = err.message;
        res.json(json);
        connection.release(); //释放连接
      } else {
        json['error_code'] = 0;
        json['result'] = result;
        res.json(json);
        connection.release();
      }
    });


  });

});

//添加到数据库
router.get('/add', function (req, res, next) {
  var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    port: '3306',
    database: 'nodetest'
  });

  connection.connect();

  var addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
  var addSqlParams = ['菜鸟工具', 'https://c.runoob.com', 'hh', 'CN'];


  let json = {};

  connection.query(addSql, addSqlParams, function (err, result) {
    if (err) {
      json['error_code'] = 1;
      json['result'] = err.message;
      res.json(json);
    } else {
      json['error_code'] = 0;
      res.json(json);

    }
  });
  connection.end();
});








module.exports = router;